OpenEdge Data Management:<br />DataServer for Microsoft SQL Server Data types

MS SQL Server data types differ from OpenEdge data types. However, each data type supported by the DataServer has at least one OpenEdge equivalent.

The DataServer translates MS SQL Server data types into OpenEdge equivalents and places the mapping into the schema holder. You can access this mapping information using the Data Dictionary. For example, the Data Dictionary lists the MS SQL Server datetime and smalldatetime data types as timestamp, which is the equivalent ODBC SQL data type and not the MS SQL Server timestamp data type. There can also be some restrictions in data source compatibility with OpenEdge. For example, the OpenEdge database cannot support a numeric or decimal field defined in MS SQL Server with greater than 28 digits.

You can also modify these definitions using the Data Dictionary. For example, the DataServer maps the MS SQL Server tinyint data type to the OpenEdge equivalent, INTEGER. Suppose, however, that your application uses the tinyint field in such a way that the LOGICAL data type is a more suitable equivalent. In this case, you would change the data type from INTEGER to LOGICAL in the schema holder. If you do change a data type mapping, be sure to select a data type that accommodates the data in the column, otherwise conversion errors might occur at run time. Also, remember to specify a display format that is appropriate for the new data type. See the "Modifying a schema holder" section for an explanation of how to use the Data Dictionary to change OpenEdge data types in the schema holder.

Table 2–4 lists these data types, their ODBC SQL equivalents, and their default OpenEdge equivalents. The data types in parentheses are alternative data types that you can specify in the schema holder for your MSS data source.

Table 2–4: MS SQL Server data type equivalents
MS SQL Server data type	SQL-ODBC¹ data type	OpenEdge default
`integer`	`SQL_INTEGER`	`INTEGER (DECIMAL)`
`smallint`	`SQL_SMALLINT`	`INTEGER (DECIMAL or LOGICAL)`
`tinyint`	`SQL_TINYINT`	`INTEGER (DECIMAL or LOGICAL)`
`decimal`	`SQL_DECIMAL`	`DECIMAL (INTEGER)`
`numeric`²	`SQL_DECIMAL`	`DECIMAL (INTEGER)`
`float`³	`SQL_FLOAT`	`DECIMAL (INTEGER)`
`double precision`	`SQL_DOUBLE`	`DECIMAL (INTEGER)`
`real`	`SQL_REAL`	`DECIMAL (INTEGER)`
`char`⁴	`SQL_CHAR`	`CHARACTER`
`varchar`⁴ ⁵	`SQL_VARCHAR`	`CHARACTER`
`text`⁵ ⁶ ⁷	`SQL_LONGVARCHAR`	`CHARACTER`⁸
`money`	`SQL_DECIMAL`	`DECIMAL (INTEGER)`
`smallmoney`	`SQL_DECIMAL`	`DECIMAL (INTEGER)`
`datetime`	`SQL_TIMESTAMP`	`CHARACTER`⁹ `(DATE`¹⁰ `)`
`smalldatetime`	`SQL_TIMESTAMP`	`CHARACTER`⁹ `(DATE`¹⁰ `)`
`binary`⁴	`SQL_BINARY`	`CHARACTER`
`varbinary`	`SQL_VARBINARY`	`CHARACTER`
`image`	`SQL_LONGVARBINARY`	`CHARACTER`⁸
`bit`	`SQL_BIT`	`LOGICAL`
`timestamp`¹¹	`SQL_VARBINARY`	Unsupported
`identity`¹²	NA	Not supported

¹The SQL-ODBC data types demonstrate the mapping of native MS SQL Server data types to the ODBC standard.

²The DataServer truncates values in MS SQL Server decimal or numeric columns defined with a scale greater than 10. In the case of float columns, it reliably translates up to 10 places only.

³Do not use the float or real data types in joins, in primary keys, or with the equality operator.

⁴When you define a binary or char column to allow nulls, MSS stores the data type definitions as varbinary and varchar respectively. This does not affect how the DataServer maps the MSS data types to OpenEdge data types.

⁵When migrating an OpenEdge database to SQL Server, character fields whose format is less than x(8000) are defined as VARCHAR by default. Larger character fields are defined as TEXT. If there are several large text fields in one record that each individually fall under the size limits to be defined as VARCHAR, but in sum exceed the maximum record size for SQL Server, it will be necessary to define the fields as TEXT to successfully create the records.

⁶The amount of data that can be accessed in a field defined to be TEXT by an OpenEdge application is limited by the maximum size of a record that can be passed to the client. The maximum is 32K. The performance option BINDING cannot be used with a TEXT data type. To work around that limitation, construct field lists to exclude the retrieval of the TEXT data.

⁷The DataServer for MS SQL Server does not support the Unicode data types defined in SQL Server. While tables with the Unicode types can be pulled into an OpenEdge schema holder, trying to access the fields will cause a fatal run-time error. The client will display a message indicating a schema mismatch and bad data type selection, and abort. The abort prevents data corruption. To work around the fields with Unicode types, use the EXCEPT clause in your field lists to keep the Unicode type fields out of your result set.

⁸Although the MS SQL Server text and image fields can hold up to 2MB, OpenEdge retrieves only up to 32,000 bytes. If you are using a Progress Format phrase, there might be additional limits on the size of text and image fields. See OpenEdge Development: Progress 4GL Reference . You can use the DataServer (-Dsrv MAX_LENGTH) startup parameter to limit the amount of returned text data.

⁹By default, the initial value of an MS SQL Server datetime or smalldatetime column is the Unknown value (?). The default initial values for binary and varbinary are also the Unknown value (?). The MS SQL Server datetime and smalldatetime data types contain both date and time information. The DataServer maps these to the OpenEdge DATE data type; however, you can change the DATE data type to CHARACTER in the schema holder. If you do this, remember to change the format to match the new data type. For example, if you change the data type to CHARACTER, specify a character format, such as x(26).

¹⁰When mapping of the MS SQL Server datetime or smalldatetime data types is to the OpenEdge DATE data type, OpenEdge truncates the time portion of the date.

¹¹The DataServer considers a timestamp data type to be a hidden value. It is not visible to the user, but you can still access a MS SQL Server table that contains a timestamp column.

¹²Identity columns are limited by SQL Server to one per table. Creating an identity column to define PROGRESS_RECID is the preferred method of supporting the ROWID function. See the "ROWID function" section for more information. If your SQL Server table has an existing identity column, turn off the mandatory flag, and set its initial value to the Unknown value (?) in your schema holder to prevent the DataServer from attempting to overwrite the identity value generated by SQL Server.